import numpy as np
import pandas as pd
import pymysql

class MysqlUtile(object):
    def __init__(self):
        self.conn=pymysql.connect(
            host='127.0.0.1',
            user='root',
            password='root',
            database='scenic',
            port=3306,
            charset='utf8'
        )
    def get_data(self):
            # 获取数据
        cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql = """
        SELECT t.tourist_agency_name, rel.id_no, LEFT(rel.id_no, 2) as province_code, DAYOFWEEK(gate.create_time) as weekend,
        cast(substring(rel.id_no, 7, 4) as UNSIGNED) as birth_year
        FROM ticket_order_user_rel rel
        JOIN ticket_order t on t.id = rel.order_id
        JOIN order_user_gate_rel gate on gate.ticket_rel_id = rel.id
        WHERE t.tourist_agency_name != '' and t.pay_time != ''
        """
        cursor.execute(sql)
        rows = cursor.fetchall()
        df = pd.DataFrame(rows)
        # 周末或非周末
        df['non_weekend'] = df['weekend'].apply(lambda x: 1 if x not in [1, 7] else 0)
        # 新增有效性标记列
        df['valid_id'] = df['id_no'].apply(lambda x: 1 if len(x.split()) != '' else 0)
        # 计算是否为老年人
        df['elderly'] = df.apply(
            lambda x: 1 if(x['valid_id'] and 2024 - x['birth_year'] >= 60) else 0 if x['valid_id'] else np.nan,
            axis=1
        )
        # 计算是否为外省游客
        df['out_province'] = df.apply(
            lambda x: 1 if(x['valid_id'] and x['province_code'] != '44') else 0 if x['valid_id'] else np.nan,
            axis=1
        )
        print(df.head())
        
        result = df.groupby(['tourist_agency_name']).agg(
            toutal_visitors=('id_no','count'),
            valid_visitors=('valid_id','sum'),
            non_weekend_visitors=('non_weekend','mean'),
            elderly_visitors=('elderly', 'sum'),  # 老年人游客数
            out_province_visitors=('out_province', 'sum'),  # 外省游客数
        )
        print(result.head())
        
        # 计算老年、外省游客占比
        result['elderly_ratio'] = result['elderly_visitors'] / result['valid_visitors'].replace(0, np.nan)
        result['out_province_ratio'] = result['out_province_visitors'] / result['valid_visitors'].replace(0, np.nan)
        print(result.head())
        
        # 清理中间列
        result = result.drop(columns=['elderly_visitors', 'out_province_visitors'])
        result['out_province_ratio'] = result['out_province_ratio'].fillna(0)
        result['elderly_ratio'] = result['elderly_ratio'].fillna(0)
        result.to_csv('tourist_agency_visitors.csv')
        

if __name__ == '__main__':
    mu = MysqlUtile()
    mu.get_data()